/*
 * Copyright (c) 2006, 2022, webrx.cn All rights reserved.
 *
 */
package cn.webrx.db;

import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Objects;
import java.util.Properties;

/**
 * <p>Project: jdemo202203 - RsMetaDataDemo
 * <p>Powered by webrx On 2022-11-03 09:08:43
 *
 * @author webrx [webrx@126.com]
 * @version 1.0
 * @since 17
 */
public class RsMetaDataDemo {
    Connection conn = null;

    @BeforeEach
    void connection() {
        try {
            InputStream is = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
            Properties prop = new Properties();
            prop.load(is);
            String driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver");
            String url = prop.getProperty("db.url", "jdbc:mysql:/");
            String user = prop.getProperty("db.username", "root");
            String password = prop.getProperty("db.password", "");

            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @AfterEach
    void close() {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    void t2(){
        try{
            PreparedStatement pst = conn.prepareStatement("select * from t_student limit 2");
            ResultSet rs = pst.executeQuery();

            //接口 获取结果集对象的相关信息 库名，表名，结果有多少列 列名 列别名 列的类型
            ResultSetMetaData rsmd = rs.getMetaData();
            String dn = conn.getCatalog();
            String tn = rsmd.getTableName(1);
            while(rs.next()){
                for(int i=1;i<=rsmd.getColumnCount();i++){
                    String cn = rsmd.getColumnLabel(i);
                    System.out.println(rsmd.getColumnType(i));
                    //System.out.println(rsmd.getColumnTypeName(i));
                    //Object obj = rs.getObject(cn);
                    String type = rsmd.getColumnTypeName(i);
                    String obj = "";
                    if(type.contains("INT")){
                        obj = rs.getString(i);
                    }else if(type.equals("DATA")){
                        obj = rs.getDate(i).toLocalDate().toString();
                    }else if(type.equals("VARCHAR")) {
                        obj = rs.getString(i);
                    }else{
                        obj = rs.getObject(i).toString();
                    }

                    System.out.printf("%s %s %s = %s%n",dn,tn,cn,obj);
                }
                System.out.println("------------------------------------");
            }

        }catch(Exception e){
            e.printStackTrace();
        }
    }



    @Test
    void t() {
        try {
            //System.out.println(conn.getMetaData().getDatabaseProductVersion());
            PreparedStatement pst = conn.prepareStatement("select id sid,name uname from t_student ts");
            ResultSet rs = pst.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            System.out.println(rsmd);
            System.out.println(rsmd.getColumnCount());

            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                //数据库名称
                System.out.println(rsmd.getCatalogName(i));
                //表名
                System.out.println(rsmd.getTableName(i));
                //列别名，如果没有别名，则是列名
                System.out.println(rsmd.getColumnLabel(i));
                //列名
                System.out.println(rsmd.getColumnName(i));

                System.out.println(rsmd.getColumnTypeName(i));
                System.out.println(rsmd.getColumnDisplaySize(i));
            }


            //System.out.println(rsmd);

            PreparedStatement ps2 = conn.prepareStatement("show create table t_student");
            ResultSet rs2 = ps2.executeQuery();
            rs2.next();
            System.out.println(rs2.getString(2));
            System.out.println(rs2.getString("Create Table"));


        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
